/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

/**
 *
 * @author Administrator
 */
public class StockShopCntJdbcDao extends JdbcDaoSupport implements StockShopCntDataDao {

    public void StockShopCntData(Date adTsnDate, int aiShopId, String asShopBm,
            int aiDateNo, String asYear, int aiYybh, int aiMan_id, String asTname) {
        int iShopCnt = 0;
        int iRc = 0;
        int aiYear = 0, aiWeek = 0;
        int i = 0;
        final int isize;
        final int isize2;
        final int iyear,  iweek;
        final Date dd;

        //该程序只运行一次即可，所以目前定为2店，如2店闭店，请换为其他门店
        if (aiShopId != 2) {
            return;
        }
        String sql = "";
        String sLb = "";
        String sDate = "";
        int iYybh = 0;
        int iPz = 0;
        ArrayList<String> alLb = new ArrayList<String>();
        ArrayList<Integer> aiSC = new ArrayList<Integer>();
        ArrayList<String> alLbYyb = new ArrayList<String>();
        ArrayList<Integer> aiSCYyb = new ArrayList<Integer>();
        ArrayList<Integer> aiYyb = new ArrayList<Integer>();
        ArrayList<Integer> aiPz = new ArrayList<Integer>();

        final ArrayList<String> alLb2 = new ArrayList<String>();
        final ArrayList<Integer> aiSC2 = new ArrayList<Integer>();
        final ArrayList<String> alLbYyb2 = new ArrayList<String>();
        final ArrayList<Integer> aiSCYyb2 = new ArrayList<Integer>();
        final ArrayList<Integer> aiYyb2 = new ArrayList<Integer>();
        final ArrayList<Integer> aiPz2 = new ArrayList<Integer>();
        List lstStockShopCnt;
        List lstStockShopCntYyb;
        sDate = new SimpleDateFormat("w").format(adTsnDate);
        aiWeek = Integer.parseInt(sDate);
        //cal.set(iRc, iRc, iRc);

        System.out.println("now processing    StockCnt's Data... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        //取总销售金额
        JdbcTemplate jt = getJdbcTemplate();
        sql = "select lb,count(bm) as shopcnt from sy_spsjk where spkdate=? and sl<>0 and lb is not null group by lb";
        Object[] params = new Object[]{adTsnDate};
        int[] types = {Types.DATE};
        lstStockShopCnt = jt.query(sql, params, types, new RowMapperResultReader(new StockShopCntRM()));
        Iterator Iter = lstStockShopCnt.iterator();
        StockShopCnt ltam = new StockShopCnt();
        while (Iter.hasNext()) {
            ltam = (StockShopCnt) Iter.next();
            sLb = ltam.getLb();
            iShopCnt = ltam.getShop_cnt();
            alLb.add(sLb);
            aiSC.add(iShopCnt);
            //cls
            sLb = "";
            iShopCnt = 0;
        }
        //取各营运部和配置分别计算的
        sql = "select a.lb,b.yybh,b.pz,count(a.bm) as shopcnt from sy_spsjk a ,sy_archi_dept b where a.bm=b.bm and b.ckbj=1 and spkdate=? and sl<>0 and lb is not null group by lb,yybh,pz";
        lstStockShopCntYyb = jt.query(sql, params, types, new RowMapperResultReader(new StockShopCntRM2()));
        Iterator Iter2 = lstStockShopCntYyb.iterator();
        StockShopCnt ltam2 = new StockShopCnt();
        while (Iter2.hasNext()) {
            ltam2 = (StockShopCnt) Iter2.next();
            sLb = ltam2.getLb();
            iShopCnt = ltam2.getShop_cnt();
            iYybh = ltam2.getYybh();
            iPz = ltam2.getPz();
            alLbYyb.add(sLb);
            aiSCYyb.add(iShopCnt);
            aiYyb.add(iYybh);
            aiPz.add(iPz);
            //cls
            sLb = "";
            iShopCnt = 0;
            iYybh = 0;
            iPz = 0;
        }

        //begin insert into StockShopCnt
        //1:delete
        sql = "delete from t_bi_stock_shopcnt where tsn_date = ? ";
        iRc = jt.update(sql, params, types);
        //2:insert

        iyear = aiYear;
        iweek = aiWeek;
        dd = adTsnDate;
        //总的
        isize = alLb.size();
        for (i = 0; i < isize; i++) {
            alLb2.add(alLb.get(i));
            aiSC2.add(aiSC.get(i));
        }
        if (isize > 0) {
            sql = "insert into t_bi_stock_shopcnt " +
                    "(tsn_year,tsn_week,tsn_date,lb,shop_cnt,yybh,pz)" +
                    " values(?,?,?,?,?,?,?)";
            BatchPreparedStatementSetter setter = null;
            setter = new BatchPreparedStatementSetter() {

                public int getBatchSize() {
                    return isize;
                }

                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    ps.setInt(1, iyear);
                    ps.setInt(2, iweek);
                    ps.setDate(3, dd);
                    ps.setString(4, alLb2.get(index));
                    ps.setInt(5, aiSC2.get(index));
                    ps.setInt(6, 0);
                    ps.setInt(7, 0);
                }
            };
            jt.batchUpdate(sql, setter);
        }
        //分的
        isize2 = alLbYyb.size();
        for (i = 0; i < isize2; i++) {
            alLbYyb2.add(alLbYyb.get(i));
            aiSCYyb2.add(aiSCYyb.get(i));
            aiYyb2.add(aiYyb.get(i));
            aiPz2.add(aiPz.get(i));
        }
        if (isize2 > 0) {
            BatchPreparedStatementSetter setter2 = null;
            setter2 = new BatchPreparedStatementSetter() {

                public int getBatchSize() {
                    return isize2;
                }

                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    ps.setInt(1, iyear);
                    ps.setInt(2, iweek);
                    ps.setDate(3, dd);
                    ps.setString(4, alLbYyb2.get(index));
                    ps.setInt(5, aiSCYyb2.get(index));
                    ps.setInt(6, aiYyb2.get(index));
                    ps.setInt(7, aiPz2.get(index));
                }
                };
            jt.batchUpdate(sql, setter2);
        }
   }

    class StockShopCntRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            StockShopCnt lmx = new StockShopCnt();
            lmx.setLb(rs.getString("lb").trim());
            lmx.setShop_cnt(rs.getInt("shopcnt"));
            return lmx;
        }
    }

    class StockShopCntRM2 implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            StockShopCnt lmx = new StockShopCnt();
            lmx.setLb(rs.getString("lb").trim());
            lmx.setShop_cnt(rs.getInt("shopcnt"));
            lmx.setYybh(rs.getInt("yybh"));
            lmx.setPz(rs.getInt("pz"));
            return lmx;
        }
    }
}
